﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DelonixRegia.Classes
{
    public class StaffDBManager
    {
        private const string connectionString = "Data Source=.; Initial Catalog=DelonixRegia; Integrated Security=True;";

        public static bool createStaff(string firstName, string lastName, string nric, DateTime dateOfBirth, string bankAccNo, string address, string phoneNo, string emailAddress, int dutyType)
        {
            int rowCreated = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "INSERT INTO Staff(firstName, lastName, nric, dateOfBirth, bankAccNo, address, phoneNo, emailAddress, dutyType) VALUES (@firstName, @lastName, @nric, @dateOfBirth, @bankAccNo, @address, @phoneNo, @emailAddress, @dutyType)";
                comm.Parameters.AddWithValue("@firstName", firstName);
                comm.Parameters.AddWithValue("@lastName", lastName);
                comm.Parameters.AddWithValue("@nric", nric);
                comm.Parameters.AddWithValue("@dateOfBirth", dateOfBirth);
                comm.Parameters.AddWithValue("@bankAccNo", bankAccNo);
                comm.Parameters.AddWithValue("@address", address);
                comm.Parameters.AddWithValue("@phoneNo", phoneNo);
                comm.Parameters.AddWithValue("@emailAddress", emailAddress);
                comm.Parameters.AddWithValue("@dutyType", dutyType);

                rowCreated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (rowCreated == 1);
        }

        public static bool checkDuplicateStaff(string nric)
        {
            int noOfRows = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT COUNT(*) FROM Staff WHERE nric = @nric";
                comm.Parameters.AddWithValue("@nric", nric);

                noOfRows = (int)comm.ExecuteScalar();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (noOfRows > 0);
        }

        public static Staff SearchStaffByID(int staffID)
        {
            Staff s = null;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connectionString;
            conn.Open();

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;

            comm.CommandText = "SELECT * from Staff WHERE staffID = @staffID";
            comm.Parameters.AddWithValue("@staffID", staffID);

            SqlDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                s = new Staff();
                s.staffID = Convert.ToInt32(dr[0]);
                s.firstName = dr[1].ToString();
                s.lastName = dr[2].ToString();
                s.nric = dr[3].ToString();
                s.dateOfBirth = Convert.ToDateTime(dr[4]);
                s.bankAccNo = dr[5].ToString();
                s.address = dr[6].ToString();
                s.phoneNo = dr[7].ToString();
                s.emailAddress = dr[8].ToString();
                s.dutyType = Convert.ToInt32(dr[9]);
            }
            dr.Close();
            conn.Close();
            return s;
        }

        public static int EditStaff(Staff s)
        {

            int rowupdated = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "UPDATE Staff SET firstName = @firstName, lastName = @lastName, nric = @nric, dateOfBirth = @dateOfBirth, bankAccNo = @bankAccNo, address = @address, phoneNo = @phoneNo, emailAddress = @emailAddress, dutyType = @dutyType WHERE staffID=@staffID";

                comm.Parameters.AddWithValue("@staffID", s.staffID);
                comm.Parameters.AddWithValue("@firstName", s.firstName);
                comm.Parameters.AddWithValue("@lastName", s.lastName);
                comm.Parameters.AddWithValue("@nric", s.nric);
                comm.Parameters.AddWithValue("@dateOfBirth", s.dateOfBirth);
                comm.Parameters.AddWithValue("@bankAccNo", s.bankAccNo);
                comm.Parameters.AddWithValue("@address", s.address);
                comm.Parameters.AddWithValue("@phoneNo", s.phoneNo);
                comm.Parameters.AddWithValue("@emailAddress", s.emailAddress);
                comm.Parameters.AddWithValue("@dutyType", s.dutyType);

                rowupdated = comm.ExecuteNonQuery();

                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowupdated;
        }

        public static int DeleteStaff(int staffID)
        {
            int rowsdeleted = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Staff WHERE staffID=@staffID";

                comm.Parameters.AddWithValue("@staffID", staffID);

                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static bool checkStaffWithShift(int staffID)
        {
            int noOfRows = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT COUNT(*) FROM Shift WHERE staffID = @staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);

                noOfRows = (int)comm.ExecuteScalar();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (noOfRows > 0);
        }

        public static int GetDutyTypeByFirstName(string firstName)
        {
            int dutyType = 0;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connectionString;
            conn.Open();

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;

            comm.CommandText = "SELECT dutyType from Staff WHERE firstName = @firstName";
            comm.Parameters.AddWithValue("@firstName", firstName);

            SqlDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                Staff s = new Staff();
                s.dutyType = Convert.ToInt32(dr[0]);
                dutyType = s.dutyType;
            }
            dr.Close();
            conn.Close();
            return dutyType;
        }

        public static bool createShift(DateTime startDateTime, DateTime endDateTime, int staffID, int dutyType)
        {
            int rowCreated = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "INSERT INTO Shift(startDateTime, endDateTime, staffID, dutyType) VALUES (@startDateTime, @endDateTime, @staffID, @dutyType)";
                comm.Parameters.AddWithValue("@startDateTime", startDateTime);
                comm.Parameters.AddWithValue("@endDateTime", endDateTime);
                comm.Parameters.AddWithValue("@staffID", staffID);
                comm.Parameters.AddWithValue("@dutyType", dutyType);

                rowCreated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (rowCreated == 1);
        }

        public static DataTable getShiftByDay(string date)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT shiftID AS [Shift ID], startDateTime AS [Start Date Time], endDateTime AS [End Date Time], firstName AS [Staff Name], name AS [Duty Type] FROM Shift, Staff, DutyType WHERE Shift.staffID = Staff.staffID AND Shift.dutyType = DutyType.dutyTypeID AND CONVERT(VARCHAR(25), startDateTime, 126) LIKE @date + '%'";

                comm.Parameters.AddWithValue("@date", date);

                SqlDataAdapter adapter = new SqlDataAdapter(comm);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
    }
}